package Timesheet.util;

import Timesheet.TimesheetDailyReport;
import Timesheet.TimesheetDailyReportLine;
import Timesheet.TimesheetPeriodReport;

import application.DBConnectionFactory;
import application.LifeCycleListenerImpl;

import com.sun.util.logging.Level;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.text.DateFormat;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import oracle.adfmf.util.Utility;
import oracle.adfmf.util.logging.Trace;

public class TimesheetDbTransaction {
    private static Connection conn;
    private static final String MT_TIMESHEET_TABLE = "MT_TIMESHEET_HDR";
    private static final String MT_CALENDAR_TABLE = "MT_CALENDAR";
    private static final String MT_TIMESHEET_DAILY = "MT_TIMESHEET_DAILY";
    private static final String MT_TIMESHEET_DAILY_LINE = "MT_TIMESHEET_DAILY_LINE";
    private static final int maxRecords = 10;
    private static final String dateFormat = "yyyy-MM-dd HH:mm:ss";
    private static DateFormat dfDb = new SimpleDateFormat(dateFormat);

    public TimesheetDbTransaction() {
        super();
        try {
            conn = DBConnectionFactory.getConnection();
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class, "TimesheetDbTransaction",
                      "############## Exception:  TimesheetDbTransaction(): " + e.getMessage());
        }
    }

    private String dateToDbString(Date date) {

        return (null == date ? " " : dfDb.format(date));
    }

    private Date dbStringToDate(String dateString) {
        Date date = null;
        try {
            date = (new SimpleDateFormat(dateFormat)).parse(dateString);
        } catch (ParseException e) {
            System.err.println(e.getMessage());
        }
        return date;
    }

    public TsCalendar selectTsCalendarFromDbByPeriodSeq(int periodSeq) {
        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "selectTsCalendarByPeriodSeq",
                  "############## Inside selectTsCalendarByPeriodSeq()");

        String selectStatement = MessageFormat.format("SELECT * FROM {0} CL WHERE CL.PERIOD_SEQ = ?", new Object[] {
                                                      MT_CALENDAR_TABLE });

        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "selectTsCalendarByPeriodSeq",
                  "############## " + selectStatement);

        ArrayList tsCalendarListResult = new ArrayList();
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ps.setInt(1, periodSeq);

            ResultSet rs = ps.executeQuery();
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "selectTsCalendarByPeriodSeq", "############## Query Executed.");

            while (rs.next()) {
                TsCalendar tsCalendar = parseCalendar(rs);
                tsCalendarListResult.add(tsCalendar);
            }
            rs.close(); // TODO move to finally{}
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "selectTsCalendarListBetweenDates", "############## selectTsCalendarByPeriodSeq completed.");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                      "selectTsCalendarByPeriodSeq", "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                      "selectTsCalendarByPeriodSeq", "##############Exception:  " + e.getMessage());
        }
        return (TsCalendar) tsCalendarListResult.get(0);
    }

    public ArrayList selectTsCalendarListBetweenDates(Date listStartDt, Date listEndDt, int maxRecords) {
        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                  "selectTsCalendarListBetweenDates", "############## Inside selectTsCalendarListBetweenDates()");
        String whereClause = " " + "WHERE START_DT >= ? AND END_DT <= ?";
        String orderBy = " " + "ORDER BY START_DT DESC, PERIOD_SEQ";
        String limit = " " + "LIMIT" + " " + maxRecords;
        String selectStatement = "SELECT * FROM " + MT_CALENDAR_TABLE + whereClause + orderBy + limit;
        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                  "selectTsCalendarListBetweenDates", "############## " + selectStatement);

        ArrayList tsCalendarListResult = new ArrayList();
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ps.setString(1, dateToDbString(listStartDt));
            ps.setString(2, dateToDbString(listEndDt));

            ResultSet rs = ps.executeQuery();
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "selectTsCalendarListBetweenDates", "############## Query Executed.");

            while (rs.next()) {
                TsCalendar tsCalendar = parseCalendar(rs);
                tsCalendarListResult.add(tsCalendar);
            }
            rs.close(); // TODO move to finally{}
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "selectTsCalendarListBetweenDates", "############## selectTsCalendarListBetweenDates completed.");

        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                      "selectTsCalendarListBetweenDates", "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                      "selectTsCalendarListBetweenDates", "##############Exception:  " + e.getMessage());
        }
        return tsCalendarListResult;
    }

    private TsCalendar parseCalendar(ResultSet rs) throws SQLException {
        String periodType = rs.getString("PERIOD_TYPE");
        String periodID = rs.getString("PERIOD_ID");
        Date startDt = dbStringToDate(rs.getString("START_DT"));
        Date endDt = dbStringToDate(rs.getString("END_DT"));
        int periodSeq = rs.getInt("PERIOD_SEQ");
        return new TsCalendar(periodType, periodID, startDt, endDt, periodSeq);
    }


    public ArrayList selectAvailableTsCalendarListFromDb() {
        String selectStatement =
            MessageFormat.format("SELECT * FROM {0} CL WHERE NOT EXISTS (SELECT {2} FROM {1} HDR WHERE HDR.PERIOD_SEQ = CL.PERIOD_SEQ) ORDER BY CL.PERIOD_SEQ", new Object[] {
                                 MT_CALENDAR_TABLE, MT_TIMESHEET_TABLE, "'X'"
        });
        ArrayList tsCalendarListResult = new ArrayList();
        try {
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement(selectStatement);
                rs = ps.executeQuery();
                // Tested in Eclipse - rs.next() returns "false", if there's nothing returned by the SQL.
                while (rs.next()) {
                    TsCalendar tsCalendar = parseCalendar(rs);
                    tsCalendarListResult.add(tsCalendar);
                }
            } catch (SQLException e) {
                Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                          "selectAvailableTsCalendarListFromDb", "##############Exception:  " + e.getMessage());
            } catch (Exception e) {
                Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                          "selectAvailableTsCalendarListFromDb", "##############Exception:  " + e.getMessage());
            } finally {
                rs.close();
                ps.close();
            }
        } catch (SQLException e) {
            System.err.println(e.getStackTrace());
        }
        return tsCalendarListResult;
    }


    private HashMap getCalendarHashMap(ArrayList tsCalendarList) {
        HashMap tsCalendarHashMap = new HashMap();
        for (int i = 0; i < tsCalendarList.size(); i++) {
            TsCalendar cal = (TsCalendar) tsCalendarList.get(i);
            tsCalendarHashMap.put(Integer.toString(cal.getPeriodSeq()), cal);
        }
        return tsCalendarHashMap;
    }

    public ArrayList selectTimesheetListBetweenDates(Date listStartDt, Date listEndDt, int maxRecords) {
        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                  "selectTimesheetListBetweenDates", "############## Inside selectTimesheetListBetweenDates()");

        ArrayList tsCalendarList = selectTsCalendarListBetweenDates(listStartDt, listEndDt, maxRecords);
        HashMap tsCalendarHashMap = getCalendarHashMap(tsCalendarList);

        String selectStatement =
            MessageFormat.format("SELECT * FROM {0} TS WHERE TS.PERIOD_SEQ IN (SELECT CL.PERIOD_SEQ FROM {1} CL WHERE CL.START_DT >= ? AND CL.END_DT <= ?) ORDER BY TS.SHEETID DESC, TS.SHEET_SEQ DESC", new Object[] {
                                 MT_TIMESHEET_TABLE, MT_CALENDAR_TABLE
        });

        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                  "selectTimesheetListBetweenDates", "############## " + selectStatement);

        ArrayList tsListResult = new ArrayList();
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ps.setString(1, dateToDbString(listStartDt));
            ps.setString(2, dateToDbString(listEndDt));
            ResultSet rs = ps.executeQuery();
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "selectTimesheetListBetweenDates", "############## Query Executed.");
            while (rs.next()) {

                int sheetID = rs.getInt("SHEETID");
                int sheetSeq = rs.getInt("SHEET_SEQ");
                int periodSeq = rs.getInt("PERIOD_SEQ");
                // TODO -- should keep calendar information separated from
                // PeriodReport?
                TsCalendar tsCalendar = (TsCalendar) tsCalendarHashMap.get(Integer.toString(periodSeq));
                String periodType = tsCalendar.getPeriodType();
                Date startDt = tsCalendar.getStartDate();
                Date endDt = tsCalendar.getEndDate();
                // end -- TODO
                String reportedStatus = rs.getString("REPORTED_STATUS");
                Date creationDt = dbStringToDate(rs.getString("CREATED_DT"));
                Date submissionDt = dbStringToDate(rs.getString("SUBMITTED_DT"));
                String approverOprid = rs.getString("APPROVER_OPRID");
                Date approvalActionDt = dbStringToDate(rs.getString("APPROVAL_ACTION_DT"));
                String rejectionReason = rs.getString("REJECTION_REASON");
                String comment = rs.getString("COMMENT");

                TimesheetPeriodReport tsPrdReport =
                    new TimesheetPeriodReport(sheetID, sheetSeq, periodType, periodSeq, startDt, endDt, reportedStatus,
                                              creationDt, submissionDt, approverOprid, approvalActionDt,
                                              rejectionReason, comment);
                tsListResult.add(tsPrdReport);
            }
            rs.close(); // TODO move to finally{}
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "selectTimesheetListBetweenDates", "############## selectTimesheetListBetweenDates completed.");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                      "selectTimesheetListBetweenDates", "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, TimesheetDbTransaction.class,
                      "selectTimesheetListBetweenDates", "##############Exception:  " + e.getMessage());
        }
        for (int i = 0; i < tsListResult.size(); i++) {
            loadTimesheetDetails((TimesheetPeriodReport) tsListResult.get(i));
        }
        return tsListResult;
    }

    void loadTimesheetDetails(TimesheetPeriodReport tsPrdReport) {
        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "loadTimesheetDetails",
                  "############## Inside loadTimesheetDetails()");
        String selectStatement =
            MessageFormat.format("SELECT * FROM {0} WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ? ORDER BY SHEETID, SHEET_SEQ DESC, PERIOD_SEQ, DUR", new Object[] {
                                 MT_TIMESHEET_DAILY });
        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "loadTimesheetDetails",
                  "############## " + selectStatement);

        ArrayList tsDailyListResult = new ArrayList();
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ps.setInt(1, tsPrdReport.getSheetID());
            ps.setInt(2, tsPrdReport.getSheetSeq());
            ps.setInt(3, tsPrdReport.getPeriodSeq());
            ResultSet rs = ps.executeQuery();
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "loadTimesheetDetails",
                      "############## Query Executed.");
            while (rs.next()) {
                int sheetID = rs.getInt("SHEETID");
                int sheetSeq = rs.getInt("SHEET_SEQ");
                int periodSeq = rs.getInt("PERIOD_SEQ");
                Date dur = dbStringToDate(rs.getString("DUR"));
                String reportedStatus = rs.getString("REPORTED_STATUS");
                double scheduledQty = rs.getDouble("SCHEDULED_QTY");
                /*
				 * TODO -- scheduledQty should be derived by Work Group definition
				 */
                double totalReportedQty = rs.getDouble("TOTAL_RPTD_QTY");
                String comment = rs.getString("COMMENT");
                tsDailyListResult.add(new TimesheetDailyReport(sheetID, sheetSeq, periodSeq, dur, reportedStatus,
                                                               scheduledQty, totalReportedQty, comment));
            }
            rs.close(); // TODO move to finally{}
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "loadTimesheetDetails",
                      "############## loadTimesheetDetails completed.");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "loadTimesheetDetails",
                      "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "loadTimesheetDetails",
                      "##############Exception:  " + e.getMessage());
        }
        for (int i = 0; i < tsDailyListResult.size(); i++) {
            loadTimesheetDailyReportDetails((TimesheetDailyReport) tsDailyListResult.get(i));
        }
        tsPrdReport.setTsDailyReportList(tsDailyListResult);
    }

    void loadTimesheetDailyReportDetails(TimesheetDailyReport dailyReport) {
        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                  "loadTimesheetDailyReportDetails", "############## Inside loadTimesheetDailyReportDetails()");

        String selectStatement =
            MessageFormat.format("SELECT * FROM {0} WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ? AND DUR = ? ORDER BY SHEETID, SHEET_SEQ DESC, PERIOD_SEQ, DUR, LINE_NBR", new Object[] {
                                 MT_TIMESHEET_DAILY_LINE });

        Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                  "loadTimesheetDailyReportDetails", "############## " + selectStatement);

        ArrayList tsDailyLineListResult = new ArrayList();
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ps.setInt(1, dailyReport.getSheetID());
            ps.setInt(2, dailyReport.getSheetSeq());
            ps.setInt(3, dailyReport.getPeriodSeq());
            ps.setString(4, dateToDbString(dailyReport.getDateUnderReport()));
            ResultSet rs = ps.executeQuery();
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "loadTimesheetDailyReportDetails", "############## Query Executed.");
            while (rs.next()) {
                int sheetID = rs.getInt("SHEETID");
                int sheetSeq = rs.getInt("SHEET_SEQ");
                int periodSeq = rs.getInt("PERIOD_SEQ");
                Date dur = dbStringToDate(rs.getString("DUR"));
                int lineNbr = rs.getInt("LINE_NBR");
                double reportedQty = rs.getDouble("RPTD_QTY"); // TODO -- get
                // Scheduled_Qty
                // is derived by
                // Work Group
                // definition
                String taskGroup = rs.getString("TASK_GROUP");
                String trc = rs.getString("TRC");
                String billableIndicator = rs.getString("BILLABLE_IND");
                String projectID = rs.getString("PROJECT_ID");
                String activityID = rs.getString("ACTIVITY_ID");
                String comment = rs.getString("COMMENT");
                tsDailyLineListResult.add(new TimesheetDailyReportLine(sheetID, sheetSeq, periodSeq, dur, lineNbr,
                                                                       reportedQty, taskGroup, trc, billableIndicator,
                                                                       projectID, activityID, comment));

            }
            rs.close(); // TODO move to finally{}
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "loadTimesheetDailyReportDetails", "############## loadTimesheetDailyReportDetails completed.");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class,
                      "loadTimesheetDailyReportDetails", "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class,
                      "loadTimesheetDailyReportDetails", "##############Exception:  " + e.getMessage());
        }
        dailyReport.setTsLines(tsDailyLineListResult);
    }

    public int selectSheetIDFromDbByPeriodSeq(int periodSeq) {
        String selectStatement =
            MessageFormat.format("SELECT DISTINCT(SHEETID) AS SHEETID FROM {0} WHERE PERIOD_SEQ = ?", new Object[] {
                                 MT_TIMESHEET_TABLE });
        int sheetID = -1; // return -1, if not found.
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ps.setInt(1, periodSeq);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String testStr = rs.getString("SHEETID");
                sheetID = (null == testStr ? sheetID : rs.getInt("SHEETID"));
            }
            rs.close(); // TODO move to finally{}
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "selectMaxSheetIDFromDb",
                      "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "selectMaxSheetIDFromDb",
                      "##############Exception:  " + e.getMessage());
        }
        return sheetID;
    }

    public int selectMaxSheetIDFromDb() {
        String selectStatement = MessageFormat.format("SELECT MAX(SHEETID) AS SHEETID FROM {0}", new Object[] {
                                                      MT_TIMESHEET_TABLE });
        int maxSheetID = 0; // returns 0 if no records in table. ->
        // The first sheetID starts from 1.
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String testStr = rs.getString("SHEETID");
                maxSheetID = (null == testStr ? maxSheetID : rs.getInt("SHEETID"));
            }
            rs.close(); // TODO move to finally{}
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "selectMaxSheetIDFromDb",
                      "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "selectMaxSheetIDFromDb",
                      "##############Exception:  " + e.getMessage());
        }
        return maxSheetID;
    }

    public int selectMaxSheetSeqByPeriodSeq(int periodSeq, int sheetID) {
        String selectStatement =
            MessageFormat.format("SELECT MAX(SHEET_SEQ) AS SHEET_SEQ FROM {0} WHERE PERIOD_SEQ = ? AND SHEETID = ? ", new Object[] {
                                 MT_TIMESHEET_TABLE });
        int maxSheetSeq = -1; // returns -1, if periodSeq not found in existing
        // DB.
        try {
            PreparedStatement ps = conn.prepareStatement(selectStatement);
            ps.setInt(1, periodSeq);
            ps.setInt(2, sheetID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String testStr = rs.getString("SHEET_SEQ");
                maxSheetSeq = (null == testStr ? maxSheetSeq : rs.getInt("SHEET_SEQ"));
            }
            rs.close(); // TODO move to finally{}
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "selectMaxSheetIDFromDb",
                      "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "selectMaxSheetIDFromDb",
                      "##############Exception:  " + e.getMessage());
        }
        return maxSheetSeq;
    }

    boolean exsits(Object o) {
        boolean ret = false;
        PreparedStatement ps = null;
        String selectStatement = "";
        try {
            if (o.getClass() == TimesheetPeriodReport.class) {
                TimesheetPeriodReport pdRpt = (TimesheetPeriodReport) o;
                selectStatement =
                    MessageFormat.format("SELECT DISTINCT SHEETID FROM {0} WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ?", new Object[] {
                                         MT_TIMESHEET_TABLE });
                ps = conn.prepareStatement(selectStatement);
                ps.setInt(1, pdRpt.getSheetID());
                ps.setInt(2, pdRpt.getSheetSeq());
                ps.setInt(3, pdRpt.getPeriodSeq());

            } else if (o.getClass() == TimesheetDailyReport.class) {
                TimesheetDailyReport dailyRpt = (TimesheetDailyReport) o;
                selectStatement =
                    MessageFormat.format("SELECT DISTINCT SHEETID FROM {0} WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ? AND DUR = ?", new Object[] {
                                         MT_TIMESHEET_DAILY });
                ps = conn.prepareStatement(selectStatement);
                ps.setInt(1, dailyRpt.getSheetID());
                ps.setInt(2, dailyRpt.getSheetSeq());
                ps.setInt(3, dailyRpt.getPeriodSeq());
                ps.setString(4, dateToDbString(dailyRpt.getDateUnderReport()));

            } else if (o.getClass() == TimesheetDailyReportLine.class) {
                TimesheetDailyReportLine line = (TimesheetDailyReportLine) o;
                selectStatement =
                    MessageFormat.format("SELECT DISTINCT SHEETID FROM {0} WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ? AND DUR = ? AND LINE_NBR = ?", new Object[] {
                                         MT_TIMESHEET_DAILY_LINE });
                ps = conn.prepareStatement(selectStatement);
                ps.setInt(1, line.getSheetID());
                ps.setInt(2, line.getSheetSeq());
                ps.setInt(3, line.getPeriodSeq());
                ps.setString(4, dateToDbString(line.getDateUnderReport()));
                ps.setInt(5, line.getLineNbr());
            }

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String testStr = rs.getString("SHEETID");
                ret = (null == testStr ? false : true);
            }
            rs.close(); // TODO move to finally{}
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "exsits",
                      "############## exsits() completed");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "exsits",
                      "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "exsits",
                      "##############Exception:  " + e.getMessage());
        }
        return ret;
    }

    public boolean saveOrUpdatePeriodReportToDb(TimesheetPeriodReport pdReport, String status) {
        boolean ret = false;
        try {
            conn.setAutoCommit(false);
            String sqlStatement = "";
            PreparedStatement pStmt = null;

            if (exsits(pdReport)) {
                sqlStatement =
                    MessageFormat.format("UPDATE {0} SET REPORTED_STATUS = ?, CREATED_OPRID = ?, CREATED_DT = ?, SUBMITTED_OPRID = ?, SUBMITTED_DT = ?, APPROVER_OPRID = ?, APPROVAL_ACTION_DT = ?, LASTUPD_OPRID = ?, LASTUPD_DT = ?, REJECTION_REASON = ?, COMMENT = ? WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ?", new Object[] {
                                         MT_TIMESHEET_TABLE });
                pStmt = conn.prepareStatement(sqlStatement);
                pStmt.setString(1, status);
                pStmt.setString(2, pdReport.getCreationOprid());
                pStmt.setString(3, dateToDbString(pdReport.getCreationDt()));
                pStmt.setString(4, pdReport.getSubmissionOprid());
                pStmt.setString(5, dateToDbString(pdReport.getSubmissionDt()));
                pStmt.setString(6, pdReport.getApproverOprid());
                pStmt.setString(7, dateToDbString(pdReport.getApprovalActionDt()));
                pStmt.setString(8, pdReport.getLastUpdOprid());
                pStmt.setString(9, dateToDbString(pdReport.getLastUpdDt()));
                pStmt.setString(10, pdReport.getRejectionReason());
                pStmt.setString(11, pdReport.getComment());
                pStmt.setInt(12, pdReport.getSheetID());
                pStmt.setInt(13, pdReport.getSheetSeq());
                pStmt.setInt(14, pdReport.getPeriodSeq());
            } else {
                sqlStatement =
                    MessageFormat.format("INSERT INTO {0} (SHEETID, SHEET_SEQ, PERIOD_SEQ, REPORTED_STATUS, CREATED_OPRID, CREATED_DT, SUBMITTED_OPRID, SUBMITTED_DT, APPROVER_OPRID, APPROVAL_ACTION_DT, LASTUPD_OPRID, LASTUPD_DT, REJECTION_REASON, COMMENT) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new Object[] {
                                         MT_TIMESHEET_TABLE });
                pStmt = conn.prepareStatement(sqlStatement);
                pStmt.setInt(1, pdReport.getSheetID());
                pStmt.setInt(2, pdReport.getSheetSeq());
                pStmt.setInt(3, pdReport.getPeriodSeq());
                pStmt.setString(4, status);
                pStmt.setString(5, pdReport.getCreationOprid());
                pStmt.setString(6, dateToDbString(pdReport.getCreationDt()));
                pStmt.setString(7, pdReport.getSubmissionOprid());
                pStmt.setString(8, dateToDbString(pdReport.getSubmissionDt()));
                pStmt.setString(9, pdReport.getApproverOprid());
                pStmt.setString(10, dateToDbString(pdReport.getApprovalActionDt()));
                pStmt.setString(11, pdReport.getLastUpdOprid());
                pStmt.setString(12, dateToDbString(pdReport.getLastUpdDt()));
                pStmt.setString(13, pdReport.getRejectionReason());
                pStmt.setString(14, pdReport.getComment());
            }

            pStmt.execute();
            conn.commit();
            pStmt.close(); // TODO move to finally{}
            TimesheetDailyReport[] dailyReports = pdReport.getTsDailyReportList();
            for (int i = 0; i < dailyReports.length; i++) {
                saveOrUpdateDailyReportToDb(dailyReports[i], status);
            }

            ret = true;
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "saveOrUpdatePeriodReportToDb", "############## saveOrUpdatePeriodReportToDb completed");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class,
                      "saveOrUpdatePeriodReportToDb", "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class,
                      "saveOrUpdatePeriodReportToDb", "##############Exception:  " + e.getMessage());
        }
        return ret;

    }

    boolean saveOrUpdateDailyReportToDb(TimesheetDailyReport dailyReport, String status) {
        boolean ret = false;

        String sqlStatement = "";
        PreparedStatement pStmt = null;

        try {
            conn.setAutoCommit(false);

            if (exsits(dailyReport)) {
                sqlStatement =
                    MessageFormat.format("UPDATE {0} SET REPORTED_STATUS = ?, SCHEDULED_QTY = ?, TOTAL_RPTD_QTY = ?, COMMENT = ? WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ? AND DUR = ?", new Object[] {
                                         MT_TIMESHEET_DAILY });
                pStmt = conn.prepareStatement(sqlStatement);
                pStmt.setString(1, status);
                pStmt.setDouble(2, dailyReport.getScheduledQuantity());
                pStmt.setDouble(3, dailyReport.getTotalReportedQuantity());
                pStmt.setString(4, dailyReport.getComment());
                pStmt.setInt(5, dailyReport.getSheetID());
                pStmt.setInt(6, dailyReport.getSheetSeq());
                pStmt.setInt(7, dailyReport.getPeriodSeq());
                pStmt.setString(8, dateToDbString(dailyReport.getDateUnderReport()));
            } else {
                sqlStatement =
                    MessageFormat.format("INSERT INTO {0} (SHEETID, SHEET_SEQ, PERIOD_SEQ, DUR, REPORTED_STATUS, SCHEDULED_QTY, TOTAL_RPTD_QTY, COMMENT) VALUES (?,?,?,?,?,?,?,?)", new Object[] {
                                         MT_TIMESHEET_DAILY });
                pStmt = conn.prepareStatement(sqlStatement);
                pStmt.setInt(1, dailyReport.getSheetID());
                pStmt.setInt(2, dailyReport.getSheetSeq());
                pStmt.setInt(3, dailyReport.getPeriodSeq());
                pStmt.setString(4, dateToDbString(dailyReport.getDateUnderReport()));
                pStmt.setString(5, status);
                pStmt.setDouble(6, dailyReport.getScheduledQuantity());
                pStmt.setDouble(7, dailyReport.getTotalReportedQuantity());
                pStmt.setString(8, dailyReport.getComment());
            }

            pStmt.execute();
            conn.commit();
            pStmt.close(); // TODO move to finally{}
            TimesheetDailyReportLine[] dailyRptLines = dailyReport.getTsLines();
            for (int i = 0; i < dailyRptLines.length; i++) {
                saveOrUpdateDailyReportLineToDb(dailyRptLines[i]);
            }
            ret = true;
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class, "saveNewDailyReportToDb",
                      "##############saveNewDailyReportToDb completed");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "saveNewDailyReportToDb",
                      "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class, "saveNewDailyReportToDb",
                      "##############Exception:  " + e.getMessage());
        }
        return ret;
    }

    boolean saveOrUpdateDailyReportLineToDb(TimesheetDailyReportLine dailyReportLine) {
        boolean ret = false;
        try {
            conn.setAutoCommit(false);
            String sqlStatement = "";
            PreparedStatement pStmt = null;
            if (exsits(dailyReportLine)) {
                sqlStatement =
                    MessageFormat.format("UPDATE {0} SET RPTD_QTY = ?, TASK_GROUP = ?, TRC = ?, BILLABLE_IND = ?, PROJECT_ID = ?, ACTIVITY_ID = ?, COMMENT = ? WHERE SHEETID = ? AND SHEET_SEQ = ? AND PERIOD_SEQ = ? AND DUR = ? AND LINE_NBR = ?", new Object[] {
                                         MT_TIMESHEET_DAILY_LINE });
                pStmt = conn.prepareStatement(sqlStatement);
                pStmt.setDouble(1, dailyReportLine.getReportedQty());
                pStmt.setString(2, dailyReportLine.getTaskGroup());
                pStmt.setString(3, dailyReportLine.getTimeReportingCode());
                pStmt.setString(4, dailyReportLine.getBillableIndicator());
                pStmt.setString(5, dailyReportLine.getProjectID());
                pStmt.setString(6, dailyReportLine.getActivityID());
                pStmt.setString(7, dailyReportLine.getComment());
                pStmt.setInt(8, dailyReportLine.getSheetID());
                pStmt.setInt(9, dailyReportLine.getSheetSeq());
                pStmt.setInt(10, dailyReportLine.getPeriodSeq());
                pStmt.setString(11, dateToDbString(dailyReportLine.getDateUnderReport()));
                pStmt.setInt(12, dailyReportLine.getLineNbr());
            } else {
                sqlStatement =
                    MessageFormat.format("INSERT INTO {0} (SHEETID, SHEET_SEQ, PERIOD_SEQ, DUR, LINE_NBR, RPTD_QTY, TASK_GROUP, TRC, BILLABLE_IND, PROJECT_ID, ACTIVITY_ID, COMMENT) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", new Object[] {
                                         MT_TIMESHEET_DAILY_LINE });
                pStmt = conn.prepareStatement(sqlStatement);
                pStmt.setInt(1, dailyReportLine.getSheetID());
                pStmt.setInt(2, dailyReportLine.getSheetSeq());
                pStmt.setInt(3, dailyReportLine.getPeriodSeq());
                pStmt.setString(4, dateToDbString(dailyReportLine.getDateUnderReport()));
                pStmt.setInt(5, dailyReportLine.getLineNbr());
                pStmt.setDouble(6, dailyReportLine.getReportedQty());
                pStmt.setString(7, dailyReportLine.getTaskGroup());
                pStmt.setString(8, dailyReportLine.getTimeReportingCode());
                pStmt.setString(9, dailyReportLine.getBillableIndicator());
                pStmt.setString(10, dailyReportLine.getProjectID());
                pStmt.setString(11, dailyReportLine.getActivityID());
                pStmt.setString(12, dailyReportLine.getComment());
            }

            pStmt.execute();
            conn.commit();
            pStmt.close(); // TODO move to finally{}
            ret = true;
            Trace.log(Utility.ApplicationLogger, Level.INFO, TimesheetDbTransaction.class,
                      "saveOrUpdateDailyReportLineToDb", "############## saveOrUpdateDailyReportLineToDb completed");
        } catch (SQLException e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class,
                      "saveOrUpdateDailyReportLineToDb", "##############Exception:  " + e.getMessage());
        } catch (Exception e) {
            Trace.log(Utility.ApplicationLogger, Level.SEVERE, LifeCycleListenerImpl.class,
                      "saveOrUpdateDailyReportLineToDb", "##############Exception:  " + e.getMessage());
        }
        return ret;
    }
}
